Domain Indexes
“Domain indexes” are indexes that are not natively supported by a database system. One technique for using domain indexes in a database system is described in Extensible Indexing, which is referenced above. Using the techniques described in Extensible Indexing, user-implemented routines for maintaining and using domain indexes are registered with a database system. Such routines are referred to herein as “domain index routines”.
Once domain index routines have been registered with the database system, the database system is able to invoke the domain index routines as necessary to perform more efficient query processing. Specifically, in response to requests from a client, the database server interacts with the domain index routines to build, maintain, and employ instances of the newly defined and registered domain index type. The instances of an index type are known as index objects, and contain an index key and may contain, for each index key, an associated data segment managed by the index routines. In some implementations, the data segment may be data from a row in a table associated with the index key.
When a database system receives queries that reference a table on which a domain index has been created, an optimizer within the database system determines whether to make use of domain index to process the query. If the optimizer determines that a domain index is to be used in processing the query, then the database server can invoke the domain index routines for that index type. Queries that are processed using domain indexes are referred to herein as “domain index queries”.
The invocation of a domain index routine is referred to herein as a call-out, since the domain index routine code is not built-in to the database server. According to an approach described in Extensible Indexing, the call-outs are invocations of routines that retrieve data using existing index objects. These routines include i_open, i_close, i_start, and i_fetch.
When invoked, the domain index routines perform actions dictated by the logic internal to the domain index routines. During the execution of this logic, the routines may issue one or more queries back to the database system. Queries that are issued by the domain index routines back to the database system are referred to herein as “call-back queries”. In one approach, the call-back queries may be SQL queries.
Within the database system, a call-back query execution unit receives the call-back queries, executes the call-back queries, and returns data that satisfy the conditions of the call-back queries. The data returned by the call-back query execution unit is referred to herein as the “call-back response data”. The call-back response data typically contains identifiers identifying records in the database. The identifiers may, for example, be row identifiers (rowids) that identify rows, within relational tables managed by the database system, that satisfy the selection criteria of the call-back queries.
A domain index routine that issues a call-back receives the call-back response data, performs some steps internally according to its internal logic, and returns information to the database server routine that invoked the domain index routine. The information returned by the domain index routine is referred to herein as “call-out response data”. Typically, the call-out response data is largely based on the call-back response data. Similar to the call-back response data, the call-out response data often contains the rowids of rows, within the database, that satisfy certain criteria.
The database server receives the call-out response data, and uses the call-out response data to perform whatever additional processing is required by the query that was received by the database system. That additional processing may involve, for example, sorting rows or performing joins between rows from different tables.
Flashback Queries
Techniques have also been developed that allow users to submit queries that specify retrieval of data as of prior points in time. Those queries are referred to herein as flashback queries. One way to implement flashback queries is described in System and Method for Providing Fine-Grained Temporal Database Access, cited above.
Modifications to the SQL language allow the client to express the desired prior point in time in a flashback query, herein known as the flashback time. The SQL query language is modified to include “AS OF” to indicate that a query uses flashback. A System Change Number (SCN) or timestamp may be used to specify the flashback time. In the examples below, both queries look for rows that satisfy the predicate “WHERE eq(name, ‘Fred’)=1” as of the prior point in time stated in the query.